package org.example.demo;


import com.mysql.cj.util.StringUtils;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class CompareMysqlV5Util extends CompareDatabaseUtil{

    private static final String JDBC_PROTOCOL = "jdbc:mysql://";

    private static final String FIELD_SQL = "select * from information_schema.COLUMNS c" +
            " where c.TABLE_SCHEMA = ? and c.TABLE_NAME = ?";

    private static final String INDEX_SQL = "show index from ";

    private String sourceDatabase;
    private String targetDatabase;

    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected boolean checkParam(UrlParam sourceParam, UrlParam targetParam, String table) {
        sourceDatabase = checkUrl(sourceParam.getUrl());
        targetDatabase = checkUrl(targetParam.getUrl());
        return true;
    }

    //检查url，成功则返回连接的数据库信息
    private String checkUrl(String url){
        //检查url是否符合jdbc协议
        int idx = url.indexOf(JDBC_PROTOCOL);
        if (idx != 0) {
            throw new IllegalArgumentException("数据库连接url参数有问题！请检查是否为jdbc协议格式。");
        }
        //检查连接是否指定了数据库
        String[] split = url.split("/");
        if (split.length < 4) {
            throw new IllegalArgumentException("数据库连接url参数有问题！请检查是否指定了具体的数据库。");
        }
        return split[3].split("\\?")[0];
    }

    @Override
    protected Connection initConnection(UrlParam url) {
        Connection connection;
        try {
            connection = DriverManager.getConnection(url.getUrl(), url.getUser(), url.getPassword());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

    @Override
    protected CompareResult<String> analyzeTable(Connection source, Connection target, String tableName) {
        FileWriter writer = null;
        try {
            //判断目标数据库中是否存在对应的数据表
            if (!checkTableExist(source,tableName)) {
                throw new IllegalArgumentException("源数据库中不存在这个数据表，请检查表名是否正确");
            }
            boolean targetTableExist = checkTableExist(target, tableName);
            //打开文件流
            writer = new FileWriter("compare.sql");
            //解析表字段
            if (targetTableExist) {
                alterTableField(source,target,tableName,writer);
            } else {
                createTable(source,tableName,writer);
            }
            //解析表索引
            if (targetTableExist) {
                alterTableIndex(source,target,tableName,writer);
            } else {
                addTableIndex(source,tableName,writer);
            }
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e);
        } finally {
            if (writer != null) {
                try {
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return new CompareResult<>("compare.sql");
    }

    private void alterTableIndex(Connection source, Connection target, String tableName, FileWriter writer) throws SQLException, IOException {
        Statement sourceStatement = source.createStatement();
        Statement targetStatement = target.createStatement();
        HashMap<String, List<TableIndex>> sourceMap = transferFieldToIndex(sourceStatement.executeQuery(INDEX_SQL+tableName));
        HashMap<String, List<TableIndex>> targetMap = transferFieldToIndex(targetStatement.executeQuery(INDEX_SQL+tableName));
        Set<Map.Entry<String, List<TableIndex>>> entries = sourceMap.entrySet();
        for (Map.Entry<String, List<TableIndex>> sourceEntry : entries) {
            List<TableIndex> sourceList = sourceEntry.getValue();
            List<TableIndex> targetList = targetMap.get(sourceEntry.getKey());
            String sql = compareIndex(sourceList, targetList, tableName);
            if (!StringUtils.isNullOrEmpty(sql)) {
                //System.out.println(sql);
                writer.write(sql);
            }
            targetMap.remove(sourceEntry.getKey());
        }
        if (!targetMap.isEmpty()) {
            //多出来的索引要删掉
            Set<Map.Entry<String, List<TableIndex>>> targetEntries = targetMap.entrySet();
            for (Map.Entry<String, List<TableIndex>> entry : targetEntries) {
                List<TableIndex> indexList = entry.getValue();
                String sql = dropIndex(indexList, tableName);
                //System.out.println(sql);
                if (!StringUtils.isNullOrEmpty(sql)) {
                    writer.write(sql);
                }
            }
        }
    }

    private void addTableIndex(Connection source, String tableName, FileWriter writer) throws SQLException, IOException {
        Statement sourceStatement = source.createStatement();
        HashMap<String, List<TableIndex>> sourceMap = transferFieldToIndex(sourceStatement.executeQuery(INDEX_SQL+tableName));
        Set<Map.Entry<String, List<TableIndex>>> entries = sourceMap.entrySet();
        for (Map.Entry<String, List<TableIndex>> sourceEntry : entries) {
            List<TableIndex> sourceList = sourceEntry.getValue();
            String sql = compareIndex(sourceList, null, tableName);
            if (!StringUtils.isNullOrEmpty(sql)) {
                //System.out.println(sql);
                if (!StringUtils.isNullOrEmpty(sql)) {
                    writer.write(sql);
                }
            }
        }
    }

    //当目标数据库中不存在对应表时，生成建表语句
    private void createTable(Connection source, String tableName, FileWriter writer) throws IOException, SQLException {
        PreparedStatement sourceFieldPs = getFieldSqlStatement(source,tableName,sourceDatabase);
        HashMap<String, TableField> sourceFieldMap = null;
        sourceFieldMap = transferFieldToMap(sourceFieldPs.executeQuery());
        StringBuilder sql = new StringBuilder();
        sql.append("create table ").append(tableName).append("(\n");
        //遍历源数据表字段集合
        Set<Map.Entry<String, TableField>> sourceEntries = sourceFieldMap.entrySet();
        for (Map.Entry<String, TableField> entry : sourceEntries) {
            sql.append("    ");
            TableField tableField = entry.getValue();
            generateFieldInformation(sql,tableField);
            sql.append(",\n");
        }
        //倒数第二个多余的','需要删掉
        if (',' == sql.charAt(sql.length()-2)) {
            sql.deleteCharAt(sql.length()-2);
        }
        sql.append(");\n");
        writer.write(sql.toString());
        //System.out.println(sql);
    }

    //修改表字段
    private void alterTableField(Connection source, Connection target, String tableName, FileWriter writer) throws SQLException, IOException {
        PreparedStatement sourceFieldPs = getFieldSqlStatement(source,tableName,sourceDatabase);
        PreparedStatement targetFieldPs = getFieldSqlStatement(target,tableName,targetDatabase);
        //获取字段集合
        HashMap<String, TableField> sourceFieldMap = transferFieldToMap(sourceFieldPs.executeQuery());
        HashMap<String, TableField> targetFieldMap = transferFieldToMap(targetFieldPs.executeQuery());
        //遍历源数据表字段集合
        Set<Map.Entry<String, TableField>> sourceEntries = sourceFieldMap.entrySet();
        for (Map.Entry<String,TableField> entry : sourceEntries) {
            TableField sourceField = entry.getValue();
            TableField targetField = targetFieldMap.get(entry.getKey());
            String sql = compareField(sourceField, targetField, tableName);
            if (!StringUtils.isNullOrEmpty(sql)) {
                //System.out.println(sql);
                writer.write(sql);
            }
            targetFieldMap.remove(entry.getKey());
        }
        //如果targetMap中还有元素，说明是多出来的字段
        if (!targetFieldMap.isEmpty()) {
            Set<Map.Entry<String, TableField>> entries = targetFieldMap.entrySet();
            for (Map.Entry<String,TableField> entry : entries) {
                TableField targetField = entry.getValue();
                String sql = compareField(null, targetField, tableName);
                if (!StringUtils.isNullOrEmpty(sql)) {
                    //System.out.println(sql);
                    writer.write(sql);
                }
                targetFieldMap.remove(entry.getKey());
            }
        }
    }

    //获取查询字段元数据的PreparedStatement
    private PreparedStatement getFieldSqlStatement(Connection connection, String tableName, String database) {
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(FIELD_SQL);
            ps.setString(1,database);
            ps.setString(2,tableName);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return ps;
    }

    //将读取的行数据转换为对象并存入map中，方便后续处理
    private HashMap<String,TableField> transferFieldToMap(ResultSet resultset) throws SQLException {
        HashMap<String,TableField> map = new HashMap<>();
        while (resultset != null && resultset.next()) {
            TableField field = new TableField();
            field.setName(resultset.getString("COLUMN_NAME"));
            field.setDefaultValue(resultset.getString("COLUMN_DEFAULT"));
            field.setIsNullable(resultset.getString("IS_NULLABLE"));
            field.setDataType(resultset.getString("DATA_TYPE"));
            field.setCharacterMaxLength(resultset.getString("CHARACTER_MAXIMUM_LENGTH"));
            field.setCharacterOctetLength(resultset.getString("CHARACTER_OCTET_LENGTH"));
            field.setNumericPrecision(resultset.getString("NUMERIC_PRECISION"));
            field.setNumericScale(resultset.getString("NUMERIC_SCALE"));
            field.setDatetimePrecision(resultset.getString("DATETIME_PRECISION"));
            field.setCharacterSetName(resultset.getString("CHARACTER_SET_NAME"));
            field.setCollationName(resultset.getString("COLLATION_NAME"));
            field.setColumnType(resultset.getString("COLUMN_TYPE"));
            field.setColumnKey(resultset.getString("COLUMN_KEY"));
            field.setExtra(resultset.getString("EXTRA"));
            field.setColumnComment(resultset.getString("COLUMN_COMMENT"));
            field.setPrivileges(resultset.getString("PRIVILEGES"));
            field.setGenerationExpression(resultset.getString("GENERATION_EXPRESSION"));
            map.put(field.getName(),field);
        }
        return map;
    }

    private HashMap<String, List<TableIndex>> transferFieldToIndex(ResultSet resultSet) throws SQLException {
        HashMap<String, List<TableIndex>> map = new HashMap<>();
        while (resultSet.next()) {
            TableIndex index = new TableIndex();
            index.setIndexComment(resultSet.getString("Index_comment"));
            index.setColumnName(resultSet.getString("Column_name"));
            index.setIndexType(resultSet.getString("Index_type"));
            index.setSeqInIndex(resultSet.getInt("Seq_in_index"));
            index.setIsNull(resultSet.getString("Null"));
            index.setKeyName(resultSet.getString("Key_name"));
            index.setNonUnique(resultSet.getInt("Non_unique"));
            List<TableIndex> indexList = map.get(index.getKeyName());
            if (indexList == null) {
                indexList = new ArrayList<>();
                map.put(index.getKeyName(),indexList);
            }
            indexList.add(index);
        }
        return map;
    }

    private boolean checkTableExist(Connection conn, String tableName) throws SQLException {
        boolean flag = false;
        DatabaseMetaData metaData = conn.getMetaData();
        String[] type = {"TABLE"};
        ResultSet tables = metaData.getTables(null, null, tableName, type);
        flag = tables.next();
        return flag;
    }

    //具体比较字段的方法，如果有差异，生成ddl语句
    private String compareField(TableField source, TableField target, String tableName) {
        StringBuilder sql = new StringBuilder();
        boolean isModify = false;
        boolean targetIsNull = target == null;
        boolean sourceIsNull = source == null;
        sql.append("alter table ").append(tableName);
        //source字段不存在时要删除字段
        if (sourceIsNull && !targetIsNull) {
            sql.append(" drop ").append("column ").append(target.getName()).append(";\n");
            return sql.toString();
        }
        //target字段不存在时要添加字段
        if (targetIsNull) {
            sql.append(" add ");
        } else {
            sql.append(" modify ");
        }
        sql.append("column ");
        generateFieldInformation(sql,source);
        //判断默认值
        if (targetIsNull || !StringUtils.nullSafeEqual(source.getDefaultValue(), target.getDefaultValue())) {
            isModify = true;
        }
        //判断是否可以为空
        if (targetIsNull || !StringUtils.nullSafeEqual(source.getIsNullable(),target.getIsNullable())) {
            isModify = true;
        }
        //判断字段类型以及精度
        if (targetIsNull || !StringUtils.nullSafeEqual(source.getDataType(),target.getDataType()) ||
                !StringUtils.nullSafeEqual(source.getCharacterMaxLength(),target.getCharacterMaxLength()) ||
                !StringUtils.nullSafeEqual(source.getNumericPrecision(),target.getNumericPrecision()) ||
                !StringUtils.nullSafeEqual(source.getNumericScale(),target.getNumericScale()) ||
                !StringUtils.nullSafeEqual(source.getDatetimePrecision(),target.getDatetimePrecision())) {
            isModify = true;
        }
        //判断字符集是否相同（如果是字符类型的话）
        if (targetIsNull || !StringUtils.isNullOrEmpty(source.getCharacterSetName()) &&
                (!StringUtils.nullSafeEqual(source.getCharacterSetName(), target.getCharacterSetName()) || !StringUtils.nullSafeEqual(source.getCollationName(), target.getCollationName()))) {
            isModify = true;
        }
        //判断extra中的内容
        if (targetIsNull || !StringUtils.nullSafeEqual(source.getExtra(),target.getExtra())) {
            isModify = true;
        }
        //判断列的注释
        if (targetIsNull || !StringUtils.nullSafeEqual(source.getColumnComment(),target.getColumnComment())) {
            isModify = true;
        }
        sql.append(";\n");

        return isModify ? sql.toString() : "";
    }

    //生成字段相关的ddl语句
    private void generateFieldInformation(StringBuilder sql, TableField source) {
        sql.append(source.getName()).append(" ").append(source.getColumnType()).append(" ");
        //默认值为null的话可以不加，避免报错
        if (!StringUtils.isNullOrEmpty(source.getDefaultValue())) {
            sql.append("default ").append(source.getDefaultValue()).append(" ");
        }
        //字符集不为空说明是字符类型的字段，要带上字符集
        if (!StringUtils.isNullOrEmpty(source.getCharacterSetName())) {
            sql.append("character set ").append(source.getCharacterSetName()).append(" collate ").append(source.getCollationName()).append(" ");
        }
        //加上注释
        if (!StringUtils.isNullOrEmpty(source.getColumnComment())) {
            sql.append("comment '").append(source.getColumnComment()).append("' ");
        }
        //补充auto_increment、on update CURRENT_TIMESTAMP信息
        if (!StringUtils.isNullOrEmpty(source.getExtra())) {
            if (source.getExtra().contains("auto_increment")) {
                sql.append("auto_increment ");
            }
            if (source.getExtra().contains("on update CURRENT_TIMESTAMP")) {
                sql.append("on update CURRENT_TIMESTAMP ");
            }
        }
        //加上是否为空的指令，因为timestamp类型的字段如果不加这个，默认会带有默认值为current_timestamp。
        switch (source.getIsNullable()) {
            case "YES":
                sql.append("null ");
                break;
            case "NO":
                sql.append("not null ");
                break;
        }
    }

    private String dropIndex(List<TableIndex> indexList, String tableName) {
        return "drop index " + indexList.get(0).getKeyName() + " on " + tableName + ";\n";
    }

    private String compareIndex(List<TableIndex> source, List<TableIndex> target, String tableName) {
        boolean isModify = false;
        boolean sourceIsNull = source == null;
        boolean targetIsNull = target == null;
        StringBuilder sql = new StringBuilder();
        if (!targetIsNull) {
            sql.append(dropIndex(target,tableName));
            if (sourceIsNull) {
                return sql.toString();
            }
            target.sort((x, y) -> x.getSeqInIndex() - y.getSeqInIndex());
        }
        source.sort((x, y) -> x.getSeqInIndex() - y.getSeqInIndex());
        //拼装sql语句
        generateIndexInformation(sql,source,tableName);
        //检查是否应该使用sql语句
        if (targetIsNull || source.size() != target.size()) {
            isModify = true;
        }
        if (!isModify) {
            int size = source.size();
            //索引中有一个字段不同就需要进行矫正
            for (int i = 0; i < size && !isModify; i++) {
                isModify = checkTableIndex(source.get(i),target.get(i));
            }
        }

        return isModify ? sql.toString() : "";
    }

    //注意，调用此方法时需要传入相同下标的字段
    private boolean checkTableIndex(TableIndex source, TableIndex target) {
        boolean flag = false;
        if (!source.getNonUnique().equals(target.getNonUnique())) {
            return true;
        }
        if (!StringUtils.nullSafeEqual(source.getColumnName(),target.getColumnName())) {
            return true;
        }
        if (!StringUtils.nullSafeEqual(source.getIndexType(), target.getIndexType())) {
            return true;
        }
        return flag;
    }

    private void generateIndexInformation(StringBuilder sql, List<TableIndex> source, String tableName) {
        boolean isPrimaryKey = "PRIMARY".equals(source.get(0).getKeyName());
        sql.append("alter table ").append(tableName).append(" add ");
        //唯一索引
        if (source.get(0).getNonUnique() == 0) {
            if (isPrimaryKey) {
                sql.append("primary key ");
            }else {
                sql.append("unique ");
            }
        }
        //全文索引
        if ("FULLTEXT".equals(source.get(0).getIndexType())) {
            sql.append("fulltext ");
        }
        if (!isPrimaryKey) {
            sql.append("index ");
        }
        if (!isPrimaryKey) {
            sql.append(source.get(0).getKeyName()).append(" ");
        }
        sql.append("(");
        int n = source.size();
        for (int i = 0; i < n; i++) {
            TableIndex tableIndex = source.get(i);
            sql.append(tableIndex.getColumnName());
            if (i != n - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        if (!StringUtils.isNullOrEmpty(source.get(0).getIndexComment())) {
            sql.append(" comment ").append(source.get(0).getIndexComment());
        }
        sql.append(";\n");
    }
}
